﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.ComponentModel;
using System.IO;
using System.Drawing;

namespace EWDTAssignment.App_Code
    {

    [DataObject]
    public class DBManager
        {

        //For Update, Delete INSERT
        public static int ExecuteNonQuery(string sql, params SqlParameter[] parameters)
            {
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["eShopDB"].ConnectionString))
                {
                conn.Open();
                using (SqlCommand comm = conn.CreateCommand())
                    {
                    comm.CommandText = sql;
                    foreach (SqlParameter parameter in parameters)
                        {
                        comm.Parameters.Add(parameter);
                        }


                    return comm.ExecuteNonQuery();
                    }
                }
            }





        //For Retrive
        public static DataTable ExecuteReader(string sql, params SqlParameter[] parameters)
            {
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["eShopDB"].ConnectionString))
                {

                conn.Open();
                using (SqlCommand comm = conn.CreateCommand())
                    {
                    comm.CommandText = sql;
                    foreach (SqlParameter parameter in parameters)
                        {
                        comm.Parameters.Add(parameter);
                        }
                    SqlDataReader dr = comm.ExecuteReader();
                    DataTable dt = new DataTable();
                    dt.Load(dr);
                    dr.Close();
                    return dt;
                    }
                }
            }


        //For Count, MIN,MAX etc
        public static object ExecuteScalar(string sql, params SqlParameter[] parameters)
            {
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["eShopDB"].ConnectionString))
                {
                conn.Open();
                using (SqlCommand comm = conn.CreateCommand())
                    {
                    comm.CommandText = sql;
                    foreach (SqlParameter parameter in parameters)
                        {
                        comm.Parameters.Add(parameter);
                        }
                    return comm.ExecuteScalar();
                    }
                }
            }

        public static DataTable RetrieveCategory()
        {
            string sqlcmd = "select distinct Category from Product";
            DataTable dt = ExecuteReader(sqlcmd);
            return dt;
        }

        

        #region Promotion
        public static void CreatePromotion(double discountRate, DateTime startDay, DateTime endDay, int productID, string imgSrc)
        {
            string sqlcmd = "insert into Promotion(DiscountRate,StartTime,EndTime,ProductID,ImgSrc) values (@discountRate,@st,@et,@productID,@imgsrc)";
            int a=ExecuteNonQuery(sqlcmd,new SqlParameter("@discountRate",discountRate),new SqlParameter("@st",startDay),new SqlParameter("@et",endDay),new SqlParameter("@productID",productID),new SqlParameter("@imgsrc",imgSrc));
        }
        [DataObjectMethod(DataObjectMethodType.Select)]
        public static DataTable RetrievePromotion()
        {
            string sqlcmd = "select * from Promotion Order by StartTime desc";
            DataTable dt = ExecuteReader(sqlcmd);
            return dt;
        }
        [DataObjectMethod(DataObjectMethodType.Update)]
        public static void UpdatePromotion(double discountRate, DateTime startTime, DateTime endTime, int productID)
        {   
            string sqlcmd="update Promotion set DiscountRate=@discountRate, StartTime=@st, EndTime=@et where ProductID=@productID";
            int a = ExecuteNonQuery(sqlcmd, new SqlParameter("@discountRate", discountRate), new SqlParameter("@st", startTime), new SqlParameter("@et", endTime), new SqlParameter("@productID", productID));
        }
        [DataObjectMethod(DataObjectMethodType.Delete)]
        public static void DeletePromotion(DateTime startTime, DateTime endTime, int productID)
        {
            string sqlcmd = "delete from Promotion where StartTime=@st and EndTime=@et and ProductID=@productID";
            int a =ExecuteNonQuery(sqlcmd,new SqlParameter("@st", startTime), new SqlParameter("@et", endTime), new SqlParameter("@productID", productID));
        }
        public static DataTable retrievePromotionImg()
        {
            string sqlcmd = "select ImgSrc from Promotion where StartTime<@now and EndTime>@now";
            DataTable dt = ExecuteReader(sqlcmd, new SqlParameter("@now", DateTime.Now));
            return dt;
        }

        #endregion

        #region Product

        [DataObjectMethod(DataObjectMethodType.Select)]
        public static DataTable getProduct()
            {
            string sqlcmd = "SELECT * FROM Product ORDER BY ProductID";

            DataTable dt = ExecuteReader(sqlcmd);
            return dt;
            }


        [DataObjectMethod(DataObjectMethodType.Select)]
        public static DataTable retrieveProduct(string pCat)
            {

            string sqlcmd = "SELECT * FROM Product where @prodCat=category";

            DataTable dt = ExecuteReader(sqlcmd, new SqlParameter("@prodCat", pCat));

            return dt;


            }

        [DataObjectMethod(DataObjectMethodType.Select)]
        public static DataTable retrieveProductByID(int pID)
            {
            string sqlcmd = "SELECT * FROM Product where @prodID=ProductID";
            DataTable dt = ExecuteReader(sqlcmd, new SqlParameter("@prodID", pID));
            return dt;

            }


  



        #endregion
        #region ProductInventory
                
        public static int InsertProduct(Product p)
            {
            string sqlcmd = "INSERT INTO Product(ProdImg,ProductName,Category,Description,Price,Quantity,Flag,Specification,Type) VALUES (@prodImg,@productName,@category,@desc,@price,@quantity,@flag,@spec,@type)";
            int success = ExecuteNonQuery(sqlcmd, new SqlParameter("@prodImg", p.ProdImg), new SqlParameter("@productName", p.ProductName), new SqlParameter("@category", p.Category), new SqlParameter("@desc", p.Description), new SqlParameter("@price", p.Price), new SqlParameter("@quantity", p.Quantity), new SqlParameter("@flag", p.Flag), new SqlParameter("@spec", p.Specification), new SqlParameter("@type", p.Type));

            return success;

            }


        [DataObjectMethod(DataObjectMethodType.Update)]
        public static int UpdateProductByPID(Product p)
            {
            string sqlcmd = "UPDATE Product SET ProdImg=@pImg,ProductName=@pName,Category=@pCat,Description=@pDesc,Price=@price,Quantity=@qty,Flag=@flag,Specification=@spec,Type=@type";
            int success = ExecuteNonQuery(sqlcmd, new SqlParameter("@pImg", p.ProdImg), new SqlParameter("@pName", p.ProductName), new SqlParameter("@pcat", p.Category), new SqlParameter("@pDesc", p.Description), new SqlParameter("@price", p.Price), new SqlParameter("@qty", p.Quantity), new SqlParameter("@flag", p.Flag), new SqlParameter("@spec", p.Specification), new SqlParameter("@type", p.Type));

            return success;


            }

        [DataObjectMethod(DataObjectMethodType.Delete)]
        public static int RemoveProductFromInventory(int pID)
            {

            string sqlcom = "DELETE FROM Product WHERE ProductID =@pID";
            int success = ExecuteNonQuery(sqlcom, new SqlParameter("@pID", pID));
            return success;
            }

        public static int RemoveProductFromTableShoppingCart(int pID)
            {

            string sqlcom = "DELETE FROM ShoppingCart WHERE ProductID =@pID";
            int success = ExecuteNonQuery(sqlcom, new SqlParameter("@pID", pID));
            return success;
            }
        public static int RemoveProductFromTableSetItem(int pID)
            {

            string sqlcom = "DELETE FROM SetItem WHERE ProductID =@pID";
            int success = ExecuteNonQuery(sqlcom, new SqlParameter("@pID", pID));
            return success;
            }

        public static int RemoveProductFromTablePromotion(int pID)
            {

            string sqlcom = "DELETE FROM Promotion WHERE ProductID =@pID";
            int success = ExecuteNonQuery(sqlcom, new SqlParameter("@pID", pID));
            return success;
            }

        public static int RemoveProductFromTableComment(int pID)
            {

            string sqlcom = "DELETE FROM Comment WHERE ProductID =@pID";
            int success = ExecuteNonQuery(sqlcom, new SqlParameter("@pID", pID));
            return success;
            }


        public static int RemoveProductFromTablePurchaseOrder(int pID)
            {

            string sqlcom = "DELETE FROM PurchaseOrder WHERE ProductID =@pID";
            int success = ExecuteNonQuery(sqlcom, new SqlParameter("@pID", pID));
            return success;
            }



        public static DataTable searchProductByProductID(int pID)
            {


            string sqlcmd = "SELECT * FROM Product where productID=@pID";
            DataTable dt = ExecuteReader(sqlcmd, new SqlParameter("@pID", pID));

            return dt;

            }

        public static DataTable searchProductByProductCat(string pCat)
            {


            string sqlcmd = "SELECT * FROM Product where category=@prodCat";
            DataTable dt = ExecuteReader(sqlcmd, new SqlParameter("@prodCat", pCat));
            return dt;

            }

        public static DataTable searchProductByProductName(string pName)
            {


            string sqlcmd = "SELECT * FROM Product where ProductName LIKE @pName";
            DataTable dt = ExecuteReader(sqlcmd, new SqlParameter("@pName", "%" + pName + "%"));
            return dt;
            }


        public static DataTable searchProductByProductID_Name(int pID, string pName)
            {


            string sqlcmd = "SELECT * FROM Product where productID=@pID AND productName like @pName";
            DataTable dt = ExecuteReader(sqlcmd, new SqlParameter("@pID", pID), new SqlParameter("@pName", "%" + pName + "%"));

            return dt;

            }
        public static DataTable searchProductByProductID_Name_Type(int pID, string pName, string type)
            {


            string sqlcmd = "SELECT * FROM Product where productID=@pID AND productName like @pName AND category=@type";
            DataTable dt = ExecuteReader(sqlcmd, new SqlParameter("@pID", pID), new SqlParameter("@pName", "%" + pName + "%"), new SqlParameter("@type", type));

            return dt;

            }

        public static DataTable searchProductByProductID_Type(int pID, string type)
            {


            string sqlcmd = "SELECT * FROM Product where productid=@pid AND category=@type";
            DataTable dt = ExecuteReader(sqlcmd, new SqlParameter("@pid", pID), new SqlParameter("@type", type));

            return dt;

            }

        public static DataTable searchProductByProductName_Type(string pName, string type)
            {


            string sqlcmd = "SELECT * FROM Product where productName like @pName AND category=@type";
            DataTable dt = ExecuteReader(sqlcmd, new SqlParameter("@pName", "%" + pName + "%"), new SqlParameter("@type", type));

            return dt;

            }
        #endregion
        #region Shopping Cart

        [DataObjectMethod(DataObjectMethodType.Select)]
        public static DataTable getCartItemByAccID(int AccountID)
            {
            string sqlcmd = "SELECT * FROM Product p,ShoppingCart c,Account a WHERE a.AccountID = c.AccountID and p.ProductID = c.ProductID and c.AccountID= @AccountID";

            DataTable dt = ExecuteReader(sqlcmd, new SqlParameter("@AccountID", AccountID));
            return dt;

            }


        [DataObjectMethod(DataObjectMethodType.Update)]
        public static int EditQtyInCartItem(int AccountID, int pID, string ProductName, double Price, int CartQty, double totalItemPrice)//parameters align with the gridview ShoppingCart
            {
            
            string sqlcom = "UPDATE ShoppingCart SET cartQty=@cartQty WHERE productID=@pID AND AccountID =@accountID";
            int success = ExecuteNonQuery(sqlcom, new SqlParameter("@cartQty", CartQty), new SqlParameter("@pID", pID), new SqlParameter("@accountID", AccountID));

            return success;

            }

        //add new item to cart
        //[DataObjectMethod(DataObjectMethodType.Insert)]
        public static int InsertIntoCart(int pID, int accID, int cartQty)
            {
            using (SqlConnection conn = new SqlConnection())
                {
                int added = 0;

                try
                    {
                    conn.ConnectionString = ConfigurationManager.ConnectionStrings["eShopDB"].ConnectionString;
                    conn.Open();
                    using (SqlCommand comm = conn.CreateCommand())
                        {
                        comm.CommandText = "INSERT INTO ShoppingCart(AccountID,ProductID,CartQty) VALUES (@accID,@prodID,@cartQty)";
                       
                        comm.Parameters.AddWithValue("@prodID", pID);
                        comm.Parameters.AddWithValue("@accID", accID);
                          comm.Parameters.AddWithValue("@cartQty", cartQty);
                        int rowsAffected = comm.ExecuteNonQuery();
                        if (rowsAffected == 1)
                            {
                            added = 1;
                            }


                        }
                    }
                catch (SqlException ex)
                    {
                    throw (ex);
                    }
                return added;

                }
            }

        //sql helper Update cart if item exists in Current Acct
        [DataObjectMethod(DataObjectMethodType.Update)]
        public static int UpdateCartItem(int cartQty, int accountID, int productID)
            {
            string sqlcmd2 = "INSERT INTO ShoppingCart(AccountID,ProductID,CartQty) VALUES (@accountID,@pID,@cartQty)";


            string sqlcmd = "UPDATE ShoppingCart SET cartQty=cartQty +@cartQty WHERE productID=@pID AND AccountID =@accountID";
            int sucess = ExecuteNonQuery(sqlcmd, new SqlParameter("@cartQty", cartQty), new SqlParameter("@accountID", accountID), new SqlParameter("@pID", productID));
            if (sucess == 0)
                {
                sucess = ExecuteNonQuery(sqlcmd2, new SqlParameter("@cartQty", cartQty), new SqlParameter("@accountID", accountID), new SqlParameter("@pID", productID));
                }
            return sucess;


            }

        //Count Current User Cart item
        public static int CountCartItem(int accID)
            {
            int c = 0;
            using (SqlConnection conn = new SqlConnection())
                {
                DataTable dt = new DataTable();

                try
                    {
                    conn.ConnectionString = ConfigurationManager.ConnectionStrings["eShopDB"].ConnectionString;
                    conn.Open();
                    using (SqlCommand comm = conn.CreateCommand())
                        {
                        comm.CommandText = "SELECT COUNT(*) as total FROM Product p,ShoppingCart c,Account a WHERE a.AccountID = c.AccountID and p.ProductID = c.ProductID and c.AccountID= @accID";
                     
                        comm.Parameters.AddWithValue("@accID", accID);
                        SqlDataReader dr = comm.ExecuteReader();
                        while (dr.Read())
                            {
                            c = int.Parse(dr[0].ToString());
                            }
                        //   dt.Load(dr);
                        dr.Close();
                        }

                    }
                catch (SqlException ex)
                    {
                    throw (ex);
                    }

                return c;
                }
            }

        //delete item from cart
        public static int DeleteCartItem(int pID, int accID)
            {

            int success = 0;
            // string sqlcom = "DELETE FROM ShoppingCart WHERE AccountID=@accID AND CartID =@cartID";
            string sqlcom = "DELETE FROM ShoppingCart WHERE AccountID=@accID AND ProductID =@pID";
            success = ExecuteNonQuery(sqlcom, new SqlParameter("@accID", accID), new SqlParameter("@pID", pID));


            return success;


            }







        #endregion

        #region JiaXin


        [DataObjectMethod(DataObjectMethodType.Select)]
        public static DataTable Searchlist(String searchKey)
        {
            string sqlcmd = "Select * from Product where contains(ProductName, @Search) or contains(Category, @Search)";
            DataTable sl = ExecuteReader(sqlcmd, new SqlParameter("@Search", "%" + searchKey + "%"));
            return sl;
        }


        [DataObjectMethod(DataObjectMethodType.Update)]
        public static int UpdateProfileCust(App_Code.EditProfile e)
        {
            string sqlcmd = "UPDATE Account SET firstName=@firstName, lastName=@lastName, Gender=@Gender,DOB=@DOB,ContactNum=@ContactNum,Address=@Address,PostalCode=@PostalCode,Email=@Email where AccountID=@AccountID";
            int u = ExecuteNonQuery(sqlcmd, new SqlParameter("@firstName", e.FirstName), new SqlParameter("@lastName", e.LastName), new SqlParameter("@gender", e.Gender), new SqlParameter("@DOB", e.DOB), new SqlParameter("@contactNum", e.ContactNum), new SqlParameter("@address", e.Address), new SqlParameter("@postalCode", e.PostalCode), new SqlParameter("@email", e.Email), new SqlParameter("@accountID", e.AccountID));
            return u;
        }
        [DataObjectMethod(DataObjectMethodType.Update)]
        public static int UpdateQnA(App_Code.EditProfile e)
        {
            string sqlcmd = "Update Account SET answer=@answer, question=@question where AccountId=@AccountID";
            int ua = ExecuteNonQuery(sqlcmd, new SqlParameter("@answer", e.Answer), new SqlParameter("@question", e.Question), new SqlParameter("@accountId", e.AccountID));
            return ua;
        }
        [DataObjectMethod(DataObjectMethodType.Update)]
        public static int UpdateProfileAcc(App_Code.EditProfile e)
        {
            string sqlcmd = "Update Account SET password=@Password where AccountId=@AccountID";
            int ua = ExecuteNonQuery(sqlcmd, new SqlParameter("@password", e.Password), new SqlParameter("@accountId", e.AccountID));
            return ua;
        }
        [DataObjectMethod(DataObjectMethodType.Select)]
        public static DataTable displayProfile(int AccountId)
        {
            // EditProfile ep = null;
            string sqlcmd = "Select * from Account where AccountId=@AccountId";
            DataTable d = ExecuteReader(sqlcmd, new SqlParameter("@AccountID", AccountId));
            return d;
        }
        [DataObjectMethod(DataObjectMethodType.Select)]
        public static DataTable getAccountProfile(int AccountID)
        {
            string sqlcmd = "SELECT Password FROM Account where AccountID=@AccountID";
            DataTable dt = ExecuteReader(sqlcmd, new SqlParameter("@AccountID", AccountID));
            return dt;
        }
        [DataObjectMethod(DataObjectMethodType.Select)]
        public static DataTable getStaff()
        {
            string sqlcmd = "SELECT * FROM Account where accountType=Staff ORDER BY AccountID";

            DataTable dt = ExecuteReader(sqlcmd);
            return dt;
        }
        [DataObjectMethod(DataObjectMethodType.Insert)]
        public static int InsertNewAccount(int accountID, string AccountType, string UserName, string Password, string Question, string Answer, string FirstName, string LastName, int ContactNum, string Address, string Email, int PostalCode, DateTime DOB, string Gender)
        {
            string sqlcmd = "insert into Account (AccountType, Username, Password, Question, Answer, FirstName, LastName, ContactNum, Address, Email, PostalCode, DOB, Gender) values (@AccountType, @UserName, @Password, @Question, @Answer, @FirstName, @LastName, @ContactNum, @Address, @Position, @Email, @PostalCode, @DOB, @Gender)";
            int c = ExecuteNonQuery(sqlcmd, new SqlParameter("@AccountType", AccountType), new SqlParameter("@UserName", UserName), new SqlParameter("@Password", Password), new SqlParameter("@FirstName", FirstName), new SqlParameter("@LastName", LastName), new SqlParameter("@ContactNum", ContactNum), new SqlParameter("@Address", Address), new SqlParameter("@Email", Email), new SqlParameter("@PostalCode", PostalCode), new SqlParameter("@DOB", DOB), new SqlParameter("@Gender", Gender));
            return c;
        }
        [DataObjectMethod(DataObjectMethodType.Select)]
        public static DataTable displayStaff(string AccountType)
        {

            string sqlcmd = "Select * from Account where AccountType=@AccountType";
            DataTable d = ExecuteReader(sqlcmd, new SqlParameter("@AccountType", AccountType));
            return d;

        }
        [DataObjectMethod(DataObjectMethodType.Update)]
        public static int EditAccount(int AccountID, string AccountType, string UserName, string Password, string Question, string Answer, string FirstName, string LastName, int ContactNum, string Address, string Email, int PostalCode, DateTime DOB, string Gender)//parameters align with the gridview ShoppingCart
        {
            string sqlcom = "UPDATE  Account SET UserName=@Username, Password=@Password, Question=@Question, Answer=@Answer, FirstName=@FirstName, LastName=@LastName, ContactNum=@ContactNum, Address=@Address,Email=@Email, PostalCode=@PostalCode, DOB=@DOB, Gender=@Gender WHERE AccountId=@AccountID";
            int s = ExecuteNonQuery(sqlcom, new SqlParameter("@AccountID", AccountID), new SqlParameter("@AccountType", AccountType), new SqlParameter("@Question", AccountType), new SqlParameter("@Answer", Answer), new SqlParameter("@UserName", UserName), new SqlParameter("@Password", Password), new SqlParameter("@FirstName", FirstName), new SqlParameter("@LastName", LastName), new SqlParameter("@ContactNum", ContactNum), new SqlParameter("@Address", Address), new SqlParameter("@Email", Email), new SqlParameter("@PostalCode", PostalCode), new SqlParameter("@DOB", DOB), new SqlParameter("@Gender", Gender));

            return s;

        }
        ////////////////////////////////////////////

        [DataObjectMethod(DataObjectMethodType.Select)]
        public static DataTable getCustomer()
        {
            string sqlcmd = "SELECT * FROM Account where accountType=Customer ORDER BY AccountID";
            DataTable dt = ExecuteReader(sqlcmd);
            return dt;
        }

        [DataObjectMethod(DataObjectMethodType.Select)]
        public static DataTable displayCustomer(string AccountType)
        {

            string sqlcmd = "Select * from Account where AccountType=@AccountType";
            DataTable d = ExecuteReader(sqlcmd, new SqlParameter("@AccountType", AccountType));
            return d;

        }
        [DataObjectMethod(DataObjectMethodType.Delete)]
        public static int DelAccount(int AccountID)
        {
            string sqlcom = "Delete from Account where AccountID=@AccountID";
            int s = ExecuteNonQuery(sqlcom, new SqlParameter("@AccountID", AccountID));

            return s;
        }
        [DataObjectMethod(DataObjectMethodType.Delete)]
        public static int DelSetItem(int AccountID)
        {
            string sqlcom = "Delete from SetItem where AccountID=@AccountID";
            int s = ExecuteNonQuery(sqlcom, new SqlParameter("@AccountID", AccountID));

            return s;
        }
        [DataObjectMethod(DataObjectMethodType.Delete)]
        public static int DelShoppingCart(int AccountID)
        {
            string sqlcom = "Delete from ShoppingCart where AccountID=@AccountID";
            int s = ExecuteNonQuery(sqlcom, new SqlParameter("@AccountID", AccountID));

            return s;
        }

        [DataObjectMethod(DataObjectMethodType.Select)]
        public static SqlConnection Connect()
        {
            try
            {
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["eShopDB"].ConnectionString;
                conn.Open();
                return conn;
            }
            catch (SqlException e)
            {
                throw e;
            }

        }
        [DataObjectMethod(DataObjectMethodType.Select)]
        public static DataTable advSearchList(string pn, string cat, decimal pri)
        {
            string sqlcmd;
            DataTable d = null;

            try
                {
                if (pri > 0)//
                    {
                    if ((string.IsNullOrEmpty(pn) != true))// 11
                        {
                        if (pri > 1000)
                            {
                            sqlcmd = "select * from product where contains(Category,@cat) and contains(ProductName,@pn) and price > @pri ";
                            d = ExecuteReader(sqlcmd, new SqlParameter("@cat", cat), new SqlParameter("@pn", pn), new SqlParameter("@pri", pri));
                            }
                        else
                            {
                            sqlcmd = "select * from product where contains(Category,@cat) and contains(ProductName,@pn) and price < @pri ";
                            d = ExecuteReader(sqlcmd, new SqlParameter("@cat", cat), new SqlParameter("@pn", pn), new SqlParameter("@pri", pri));
                            }
                        }
                    else if (string.IsNullOrEmpty(pn) == true) // 01
                        {
                        if (pri > 1000)
                            {
                            sqlcmd = "select * from product where contains(Category,@cat) and price > @pri ";
                            d = ExecuteReader(sqlcmd, new SqlParameter("@cat", cat), new SqlParameter("@pri", pri));
                            }
                        else
                            {
                            sqlcmd = "select * from product where contains(Category,@cat) and price < @pri ";
                            d = ExecuteReader(sqlcmd, new SqlParameter("@cat", cat), new SqlParameter("@pri", pri));
                            }
                        }
                    }
                else if (string.IsNullOrEmpty(pn) != true)
                    {
                    if (pri == 0)//010
                        {
                        sqlcmd = "select * from product where contains(Category,@cat) and contains(ProductName,@pn) ";
                        d = ExecuteReader(sqlcmd, new SqlParameter("@cat", cat), new SqlParameter("@pn", pn));
                        }

                    }

                else if (string.IsNullOrEmpty(pn) == true && pri == 0)
                    {
                    sqlcmd = "select * from product where contains(Category,@cat)";
                    d = ExecuteReader(sqlcmd, new SqlParameter("@cat", cat));
                    }
                else
                    {
                    sqlcmd = "select * from product";
                    d = ExecuteReader(sqlcmd);
                    }
                }
            catch (Exception e)
                {
                throw e;
                }
            return d;

        }

        public static List<string> getCategory()
        {
            //String cat = "Category";
            //string sqlcmd = "Select DISTINCT @category from Product";
            //DataTable dt = ExecuteReader(sqlcmd,new SqlParameter("@category", cat));
            //return dt;
            List<string> list = new List<string>();
            SqlConnection conn = null;
            try
            {
                conn = Connect();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select distinct Category from Product;";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    list.Add(dr["Category"].ToString());
                }
                //dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }

            return list;
        }
        [DataObjectMethod(DataObjectMethodType.Select)]
        public static DataTable displayComments1(int productId)
        {

            string sqlcmd = "Select * from Comment where productId=@productId";
            DataTable d = ExecuteReader(sqlcmd, new SqlParameter("@productId", productId));
            return d;

        }
        [DataObjectMethod(DataObjectMethodType.Insert)]
        public static int InsertComment(string review, int productId, int Rating, string nick)
        {
            string sqlcmd = "insert into comment (Review, ProductID, Rating, NickName) values ( @Review,@ProductID, @Rating,@Nick)";
            int c = ExecuteNonQuery(sqlcmd, new SqlParameter("@Review", review), new SqlParameter("@productId", productId), new SqlParameter("@Rating", Rating), new SqlParameter("@Nick", nick));

            return c;
        }
    }
}

#endregion

#region timothy



#endregion








